﻿

CREATE VIEW [dbo].[fares_viewDocumenteStocuriIntrate]
AS
SELECT        IDDocumentDetaliu, IDArticol, Cantitate, ValoareUnitara, IDUnitateMasura, TermenLivrare, Observatii, ValoareUnitaraValuta, NrDoc, DataDoc, CodValuta, 
                         CantitateReceptionata, Cantitate - CantitateReceptionata AS CantitateRamasa, CodUnitate, IDPartener, IDTipDocument
FROM            (SELECT        dds.IDDocumentDetaliu, dds.IDArticol, dds.Cantitate, dds.ValoareUnitara, dds.IDUnitateMasura, dds.TermenLivrare, dds.Observatii, 
                                                    dds.ValoareUnitaraValuta, ds.NrDoc, ds.DataDoc, ds.CodValuta, ds.CodUnitate, ds.IDPartener, ds.IDTipDocument,
                                                        (SELECT        COALESCE (SUM(ddl.Cantitate), 0) AS CantitateReceptionata
                                                          FROM            dbo.DocumenteDetaliu AS ddr INNER JOIN
                                                                                    dbo.Documente AS dr ON dr.IDDocument = ddr.IDDocument INNER JOIN
                                                                                    dbo.DocumenteDetaliuLink AS ddl ON ddr.IDDocumentDetaliu = ddl.IDDocDetaliuDest AND 
                                                                                    ddl.IDDocDetaliuSursa = dds.IDDocumentDetaliu
                                                          WHERE        (dr.IDTipDocument IN (1, 13, 14)) AND (dr.AreDocGestStoc = 1) AND ddl.Tip='ST') AS CantitateReceptionata
                          FROM            dbo.DocumenteDetaliu AS dds INNER JOIN
                                                    dbo.Documente AS ds ON ds.IDDocument = dds.IDDocument
                          WHERE        (ds.IDTipDocument IN (0, 15)) AND (ds.AreDocGestStoc = 1)) AS t1